USE [QLDL]
GO

/****** Object:  UserDefinedFunction [dbo].[f_TraCuuDaiLy]    Script Date: 01/03/2011 15:34:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE function [dbo].[f_TraCuuDaiLy]
	(@TenDaiLy ntext, @MaLoaiDaiLy nvarchar(10), @DiaChi ntext, @MaQuan nvarchar(10), 
	@DienThoai varchar(20), @NgayTiepNhan date, @TienNo money)
returns @BangKetQua table
(	STT int identity(1, 1),
	MaDaiLy nvarchar(10),
	DaiLy ntext,
	Loai ntext,
	Quan ntext,
	DienThoai varchar(20),
	DiaChi ntext,
	Email varchar(50),
	NgayTiepNhan date,
	TienNo money
)
as
begin
	declare @iDaiLy int
	declare @iMaLoaiDaiLy int
	declare @iDiaChi int
	declare @iMaQuan int
	declare @iDienThoai int
	declare @iNgayTiepNhan int
	declare @iTienNo int
	
	if(@TenDaiLy is null)
		set @iDaiLy = 1
	else
		set @iDaiLy = 0
	
	if(@MaLoaiDaiLy is null)
		set @iMaLoaiDaiLy = 1
	else
		set @iMaLoaiDaiLy = 0
		
	if(@DiaChi is null)
		set @iDiaChi = 1
	else
		set @iDiaChi = 0
		
	if(@MaQuan is null)
		set @iMaQuan = 1
	else
		set @iMaQuan = 0
		
	if(@DienThoai is null)
		set @iDienThoai = 1
	else
		set @iDienThoai = 0
		
	if(@NgayTiepNhan is null)
		set @iNgayTiepNhan = 1
	else
		set @iNgayTiepNhan = 0
		
	if(@TienNo is null)
		set @iTienNo = 1
	else
		set @iTienNo = 0
	
	insert into @BangKetQua select MaDaiLy, TenDaiLy, TenLoaiDaiLy, TenQuan, DienThoai, DiaChi, Email, NgayTiepNhan, TienNo from DAILY, QUAN, LOAIDAILY
							where (DAILY.MaLoaiDaiLy = LOAIDAILY.MaLoaiDaiLy) and (DAILY.MaQuan = QUAN.MaQuan) 
							and	(@iDaiLy = 1 or TenDaiLy like @TenDaiLy) 
							and (@iMaLoaiDaiLy = 1 or DAILY.MaLoaiDaiLy = @MaLoaiDaiLy) 
							and (@iDiaChi = 1 or DiaChi like @DiaChi)
							and (@iMaQuan = 1 or DAILY.MaQuan = @MaQuan)
							and (@iDienThoai = 1 or DienThoai = @DienThoai)
							and (@iNgayTiepNhan = 1 or NgayTiepNhan = @NgayTiepNhan)
							and (@iTienNo = 1 or TienNo = @TienNo)
							
	return
end

GO


